package com.zzj.until;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelTool {

    private static POIFSFileSystem fs;
    private static HSSFWorkbook wb;
    private static HSSFSheet sheet;
    private static  HSSFRow row;

    public void getPic(String filePath,String count){
        InputStream inp;
        HSSFWorkbook workbook = null;
		try {
			inp = new FileInputStream(filePath);
			workbook = (HSSFWorkbook) WorkbookFactory.create(inp); 
			inp.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
  
        List<HSSFPictureData> pictures = workbook.getAllPictures();  
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);  
          
          
        int i = 0;  
        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {  
            HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();  
  
            if (shape instanceof HSSFPicture) {  
                HSSFPicture pic = (HSSFPicture) shape;  
                int row = anchor.getRow1();  
                //System.out.println(i + "--->" + anchor.getRow1() + ":"+ anchor.getCol1());  
                int pictureIndex = pic.getPictureIndex()-1;  
                HSSFPictureData picData = pictures.get(pictureIndex);  
  
                //System.out.println(i + "--->" + pictureIndex);  
                try {
					savePic(row, picData);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}  
            }  
            i++;  
        }  
    }
    
    public static Map<Integer, Map<Integer, String>> getPicbytes(String filePath){
    	Map<Integer, String> piconts=null;
    	Map<Integer,Map<Integer,String>> data=new HashMap<Integer,Map<Integer,String>>();
        InputStream inp;
        HSSFWorkbook workbook = null;
		try {
			inp = new FileInputStream(filePath);
			workbook = (HSSFWorkbook) WorkbookFactory.create(inp);
			inp.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
  
        List<HSSFPictureData> pictures = workbook.getAllPictures();  
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);  
        int i = 0;  
        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {  
            HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor(); 
            String base64="";
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;  
                System.out.println(i + "--->" + anchor.getRow1() + ":"+ anchor.getCol1());  
                int pictureIndex = pic.getPictureIndex()-1;  
                HSSFPictureData picData = pictures.get(pictureIndex);  
                byte[] bys = picData.getData();
                base64=new sun.misc.BASE64Encoder().encode(bys);
                piconts=new HashMap<Integer,String>();
                piconts.put((int) anchor.getCol1(),base64);
                data.put(anchor.getRow1(), piconts);
            }
            
            i++;  
        }
		return data;  
    }
    
    /**
     * 读取Excel表格表头的内容
     * @param filePath
     * @param atrow  第几行是标题（默认为0 （第一行））
     * @return String 表头内容的数组
     */
    public static String[] readExcelTitle(String filePath,int atrow) {
    	
        try {
        	InputStream is = new FileInputStream(filePath);
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(atrow);
        row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
       // System.out.println("colNum:" + colNum);
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
           // title[i] = getStringCellValue(row.getCell((short) i));
            title[i] = getCellFormatValue(row.getCell((short) i));
        }
        return title;
    }

    /**
     * 读取Excel数据内容
     * @param filePath
     * @param atrow  从第几行开始为内容（默认为从第二行开始,参数为 1）
     * @return Map 包含单元格数据内容的Map对象
     */
    public static List<List<String>> readExcelContent(String filePath,int atrow) {
    	List<List<String>> rowdata=new ArrayList<List<String>>();
    	List<String> coldata=null;

        try {
        	InputStream is = new FileInputStream(filePath);
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = atrow; i <= rowNum; i++) {
          	coldata=new ArrayList<String>();
            row = sheet.getRow(i);
            for (int j=0;j < colNum;j++) {
            	coldata.add(getCellFormatValue(row.getCell((short) j)).trim());
            }
            rowdata.add(coldata);
        }
        
        return rowdata;
    }
    
    /**
     * 保存图片
     * @param i
     * @param pic
     * @throws Exception
     */
    private static void savePic(int i, PictureData pic) throws Exception {  
    	  
        String ext = pic.suggestFileExtension();  
  
        byte[] data = pic.getData();
        if (ext.equals("jpeg")) {  
            FileOutputStream out = new FileOutputStream(  
                    "d:\\" + i + ".jpg");  
            out.write(data);  
            out.close();  
        }  
        if (ext.equals("png")) {  
            FileOutputStream out = new FileOutputStream(  
                    "d:\\" + i + ".png");  
            out.write(data);  
            out.close();  
        }  
    }  
    /**
     * 获取单元格数据内容为字符串类型的数据
     * 
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private String getStringCellValue(HSSFCell cell) {
        String strCell = "";
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            strCell = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            strCell = String.valueOf(cell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            strCell = String.valueOf(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            strCell = "";
            break;
        default:
            strCell = "";
            break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strCell;
    }

    /**
     * 获取单元格数据内容为日期类型的数据
     * 
     * @param cell
     *            Excel单元格
     * @return String 单元格数据内容
     */
    private String getDateCellValue(HSSFCell cell) {
        String result = "";
        try {
            int cellType = cell.getCellType();
            if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
                Date date = cell.getDateCellValue();
                result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
                        + "-" + date.getDate();
            } else if (cellType == HSSFCell.CELL_TYPE_STRING) {
                String date = getStringCellValue(cell);
                result = date.replaceAll("[年月]", "-").replace("日", "").trim();
            } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
                result = "";
            }
        } catch (Exception e) {
            System.out.println("日期格式不正确!");
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 根据HSSFCell类型设置数据
     * @param cell
     * @return
     */
    private static String getCellFormatValue(HSSFCell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
            // 如果当前Cell的Type为NUMERIC
            case HSSFCell.CELL_TYPE_NUMERIC:
            case HSSFCell.CELL_TYPE_FORMULA: {
                // 判断当前的cell是否为Date
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型则，转化为Data格式
                    
                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                    //cellvalue = cell.getDateCellValue().toLocaleString();
                    
                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = sdf.format(date);
                    
                }
                // 如果是纯数字
                else {
                    // 取得当前Cell的数值
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            // 如果当前Cell的Type为STRIN
            case HSSFCell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                cellvalue = cell.getRichStringCellValue().getString();
                break;
            // 默认的Cell值
            default:
                cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }
    

    
    
//	public static void main(String[] args) {
//
//		// 对读取Excel表格标题测试
//		ExcelTool excelReader = new ExcelTool();
//		String[] title = excelReader.readExcelTitle("d:\\5.xls",0);
//		System.out.println("获得Excel表格的标题:");
//		for (String s : title) {
//			System.out.print(s + " ");
//		}
//
//		// 对读取Excel表格内容测试
//		List<List<String>> contentList = excelReader.readExcelContent("d:\\5.xls",1);
//		System.out.println("获得Excel表格的内容:");
//		int j=1;//从内容位置开始
//		for(List<String> c:contentList) {
//			System.out.print(j+"--->");
//			for(String l:c){
//				System.out.println(l+" ");
//			}
//			j++;
//		}
//
//		Map<Integer, Map<Integer, String>> ma = getPicbytes("d:\\5.xls");
//		//从内容位置开始循环
//		for(int i=1;i<contentList.size()+1;i++){
//			Map<Integer, String> a = ma.get(i);
//			if(a != null && a.get(5) != null){
//				System.out.println("第"+i+"行，第5列，有图片");
//			}else{
//				System.out.println("没有图片"+i);
//			}
//
//		}
//		
//		//getPicbytes("d:\\5.xls");
//	}

}
